@file:Suppress("FunctionName", "MemberVisibilityCanBePrivate")

package com.zxy.supplier_system.server.repository

import com.zxy.supplier_system.server.controller.GoodsController
import com.zxy.supplier_system.server.controller.GoodsController.GoodsDetail.SubGoodsResponse
import com.zxy.supplier_system.server.entity.*
import com.zxy.supplier_system.server.repository.GoodsRepository.RememberedProcurementReturnPriceWithDto
import com.zxy.supplier_system.server.utils.FromBuilder
import com.zxy.supplier_system.server.utils.PathBuilder
import com.zxy.supplier_system.server.utils.SpecificationUtils
import com.zxy.supplier_system.server.utils.joinOnce
import jakarta.persistence.EntityManager
import jakarta.persistence.PersistenceContext
import jakarta.persistence.criteria.From
import jakarta.persistence.criteria.JoinType
import jakarta.persistence.criteria.Path
import org.springframework.data.jpa.domain.Specification
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.JpaSpecificationExecutor
import org.springframework.data.jpa.repository.Query
import java.math.BigDecimal
import java.time.OffsetDateTime

interface GoodsRepository : JpaRepository<Goods, Int>, JpaSpecificationExecutor<Goods>, CustomGoodsRepository {

    @Query("SELECT MAX(g.barcode) FROM Goods g WHERE g.barcode LIKE ?1%")
    fun findMaxBarcodeByPrefix(prefix: String): String?

    fun existsBySystemServiceAndBarcodeAndDisableIsFalse(systemService: SystemService, barcode: String): Boolean

    fun countAllByCategory(category: GoodsCategory): Int

    fun countAllByBrand(brand: GoodsBrand): Int

    fun findByIdAndSystemServiceAndDisableIsFalse(id: Int, systemService: SystemService): Goods?

    fun findByIdInAndSystemServiceAndDisableIsFalse(id: List<Int>, systemService: SystemService): List<Goods>

    fun findByIdInAndSystemServiceAndDisableIsTrue(id: List<Int>, systemService: SystemService): List<Goods>

    fun findByBarcodeAndSystemServiceAndDisableIsFalse(barcode: String, systemService: SystemService): Goods?

    fun countAllByBrand_Factory(factory: Factory): Int


    @Query(nativeQuery = true)
    fun findAllWithRememberedDirectSalePrice(clientId: Int): List<GoodsSummaryWithPrice>

    @Query
    fun findAllWithRememberedProcurementPurchasePrice(supplierId: Int): List<GoodsSummaryWithRememberedPurchasePrice>

    fun findByIdAndSystemServiceAndDisableIsTrue(id: Int, systemService: SystemService): Goods?

    //    @Query(nativeQuery = true)
//    fun findAllWithRememberedProcurementReturnPrice(supplierId: Int): List<Array<Any>>
//
//    fun findAllWithRememberedProcurementReturnPriceWithDto(supplierId: Int):List<RememberedProcurementReturnPriceWithDto>{
//        val list = this.findAllWithRememberedProcurementReturnPrice(supplierId)
//        return list.map {
//            RememberedProcurementReturnPriceWithDto(it[0] as Goods,it[1] as BigDecimal)
//        }
//    }
    data class RememberedProcurementReturnPriceWithDto(
        val goods: Goods,
        val subGoods: SubGoods?,
        val price: BigDecimal?,
    )

    @Query(
        "select new com.zxy.supplier_system.server.repository.GoodsAndMarketInStore(goods,market.amount)" +
                " from Goods goods left join goods.markets market where market.market.id = ?1 or market.market.id is null order by market.market.id desc "
    )
    fun getAllGoodsSummaryWithMarketInStore(marketId: Int): List<GoodsAndMarketInStore>

    @Query(
        "select new com.zxy.supplier_system.server.repository.GoodsAndMarketInStore(goods,market.amount)" +
                " from Goods goods left join goods.markets market where market.market.id = ?1 order by market.market.id desc "
    )
    fun getAllGoodsSummaryWithMarketInStoreInMarket(marketId: Int): List<GoodsAndMarketInStore>


    object Specifications {


        fun createdDateTimeBetween(startDateTime: OffsetDateTime, endDateTime: OffsetDateTime): Specification<Goods> {
            return Specification { root, _, criteriaBuilder ->
                criteriaBuilder.between(root.get("createdDateTime"), startDateTime, endDateTime)
            }
        }

        fun fromQueryGoods(
            queryGoodsRequest: GoodsController.QueryGoodsRequest
        ): Specification<Goods> {
            return Specification.allOf(
                nameOrSubGoodsNameContains(queryGoodsRequest.name),
                barcodeOrSubGoodsNumberContains(queryGoodsRequest.barcode),
                brandIn(queryGoodsRequest.brandIds),
                categoryIn(queryGoodsRequest.categoryIds),
                factoryIn(queryGoodsRequest.factoryIds),
                nameOrBarcodeWithSubGoodsContains(queryGoodsRequest.nameOrBarcode),
                notInMarketOrder(queryGoodsRequest.editMarketOrderId),
                brandInMarketOrder_MarketBrands(queryGoodsRequest.editMarketOrderId),
                brandInMarketOrder_MarketBrands(queryGoodsRequest.editMarketDeliveryOrderId),
                notInMarketDeliveryOrder(queryGoodsRequest.editMarketDeliveryOrderId),
                notInCheckInventoryRecord(queryGoodsRequest.editCheckInventoryRecordId),
                notInMarketCheckInventoryRecord(queryGoodsRequest.editMarketCheckInventoryRecordId),
                notInProcurement(queryGoodsRequest.editProcurementId),
                excludeIds(queryGoodsRequest.excludeIds),
                disable(queryGoodsRequest.disable),
            )
        }

        private fun excludeIds(excludeIds: List<Int>): Specification<Goods> {
            return Specification { root, _, criteriaBuilder ->
                if (excludeIds.isEmpty()) {
                    null
                } else {
                    criteriaBuilder.not(root.get<Int>("id").`in`(excludeIds))
                }
            }
        }

        fun brandInMarketOrder_MarketBrands(marketOrderId: Int?): Specification<Goods> {
            if (marketOrderId == null) {
                return Specification.where(null)
            }
            return Specification { root, query, criteriaBuilder ->
                val marketOrderSubquery = query.subquery(Int::class.java)
                val marketOrderRoot = marketOrderSubquery.from(MarketOrder::class.java)
                marketOrderSubquery.where(criteriaBuilder.equal(marketOrderRoot.get<Int>("id"), marketOrderId))
                val marketJoin = marketOrderRoot.join<MarketOrder, Market>("market")
                val anyIn =
                    criteriaBuilder.`in`<Any>(root.joinOnce<Goods, GoodsBrand>("brand", JoinType.LEFT).get("id"))
                anyIn.value(marketOrderSubquery.select(marketJoin.join<Market, GoodsBrand>("excludeBrands").get("id")))
                criteriaBuilder.not(anyIn)
            }
        }

        fun notInMarketOrder(marketOrderId: Int?): Specification<Goods> {
            return Specification<Goods> { root, query, criteriaBuilder ->
                if (marketOrderId == null) {
                    null
                } else {
                    val marketOrderSubquery = query.subquery(Int::class.java)
                    val marketOrderRoot = marketOrderSubquery.from(MarketOrder::class.java)
                    val marketOrderMarketOrderItemJoin =
                        marketOrderRoot.joinOnce<MarketOrder, MarketOrderItem>("goodsList")

                    val marketOrderItemGoodsJoin =
                        marketOrderMarketOrderItemJoin.joinOnce<MarketOrderItem, Goods>("goods")
                    val marketOrderItemSubGoodsJoin =
                        marketOrderMarketOrderItemJoin.joinOnce<MarketOrderItem, SubGoods>("subGoods")

                    val subGoodsSubQuery = marketOrderSubquery.subquery(Long::class.java)
                    val subGoodsRoot = subGoodsSubQuery.from(SubGoods::class.java)
                    subGoodsSubQuery.select(criteriaBuilder.count(subGoodsRoot.get<Int>("id")))
                    subGoodsSubQuery.where(
                        criteriaBuilder.equal(
                            subGoodsRoot.join<SubGoods, Goods>("goods").get<Int>("id"),
                            marketOrderItemGoodsJoin.get<Int>("id")
                        )
                    )

                    val marketOrderItemSubQuery = marketOrderSubquery.subquery(Long::class.java)
                    val marketOrderItemRoot = marketOrderItemSubQuery.from(MarketOrderItem::class.java)
                    marketOrderItemSubQuery.select(criteriaBuilder.count(marketOrderItemRoot.get<Int>("id")))
                    marketOrderItemSubQuery.where(
                        criteriaBuilder.equal(
                            marketOrderItemRoot.join<SubGoods, Goods>("goods").get<Int>("id"),
                            marketOrderItemGoodsJoin.get<Int>("id")
                        ),
                        criteriaBuilder.equal(
                            marketOrderItemRoot.join<MarketOrderItem, MarketOrder>("marketOrder").get<Int>("id"),
                            marketOrderId
                        )
                    )

                    marketOrderSubquery.select(
                        marketOrderItemGoodsJoin.get("id")
                    ).where(
                        criteriaBuilder.and(
                            criteriaBuilder.equal(
                                marketOrderRoot.get<Int>("id"),
                                marketOrderId,
                            ),
                            criteriaBuilder.or(
                                criteriaBuilder.isNull(marketOrderItemSubGoodsJoin),
                                criteriaBuilder.equal(subGoodsSubQuery, marketOrderItemSubQuery)
                            ),
                        ),
                    )

                    val `in` = criteriaBuilder.`in`<Int>(root.get("id"))
                    `in`.value(marketOrderSubquery)
                    criteriaBuilder.not(`in`)
                }
            }
        }

        fun notInMarketDeliveryOrder(marketDeliveryOrderId: Int?): Specification<Goods> {
            return Specification<Goods> { root, query, criteriaBuilder ->
                if (marketDeliveryOrderId == null) {
                    null
                } else {
                    val marketOrderSubquery = query.subquery(Int::class.java)
                    val marketOrderRoot = marketOrderSubquery.from(MarketDeliveryOrder::class.java)
                    val marketOrderMarketOrderItemJoin =
                        marketOrderRoot.joinOnce<MarketDeliveryOrder, MarketDeliveryOrderItem>("goodsList")
                    marketOrderSubquery.select(
                        marketOrderMarketOrderItemJoin.joinOnce<MarketDeliveryOrderItem, Goods>("goods").get("id")
                    ).where(
                        criteriaBuilder.equal(
                            marketOrderRoot.get<Int>("id"),
                            marketDeliveryOrderId,
                        ),
                    )
                    val `in` = criteriaBuilder.`in`<Int>(root.get("id"))
                    `in`.value(marketOrderSubquery)
                    criteriaBuilder.not(`in`)
                }
            }
        }

        fun notInCheckInventoryRecord(checkInventoryRecordId: Int?): Specification<Goods> {
            return Specification<Goods> { root, query, criteriaBuilder ->
                if (checkInventoryRecordId == null) {
                    null
                } else {
                    val subquery = query.subquery(Int::class.java)
                    val checkInventoryRecordRoot = subquery.from(CheckInventoryRecord::class.java)
                    val checkInventoryRecordCheckInventoryRecordItemJoin =
                        checkInventoryRecordRoot.joinOnce<CheckInventoryRecord, CheckInventoryRecordItem>("items")
                    subquery.select(
                        checkInventoryRecordCheckInventoryRecordItemJoin.joinOnce<CheckInventoryRecordItem, Goods>("goods")
                            .get("id")
                    ).where(
                        criteriaBuilder.equal(
                            checkInventoryRecordRoot.get<Int>("id"),
                            checkInventoryRecordId,
                        ),
                    )
                    val `in` = criteriaBuilder.`in`<Int>(root.get("id"))
                    `in`.value(subquery)
                    criteriaBuilder.not(`in`)
                }
            }
        }

        fun notInMarketCheckInventoryRecord(checkInventoryRecordId: Int?): Specification<Goods> {
            return Specification<Goods> { root, query, criteriaBuilder ->
                if (checkInventoryRecordId == null) {
                    null
                } else {
                    val subquery = query.subquery(Int::class.java)
                    val marketCheckInventoryRecordRoot = subquery.from(MarketCheckInventoryRecord::class.java)
                    val marketCheckInventoryRecordMarketCheckInventoryRecordItemJoin =
                        marketCheckInventoryRecordRoot.joinOnce<MarketCheckInventoryRecord, MarketCheckInventoryRecordItem>(
                            "items"
                        )
                    subquery.select(
                        marketCheckInventoryRecordMarketCheckInventoryRecordItemJoin.joinOnce<MarketCheckInventoryRecordItem, Goods>(
                            "goods"
                        ).get("id")
                    ).where(
                        criteriaBuilder.equal(
                            marketCheckInventoryRecordRoot.get<Int>("id"),
                            checkInventoryRecordId,
                        ),
                    )
                    val `in` = criteriaBuilder.`in`<Int>(root.get("id"))
                    `in`.value(subquery)
                    criteriaBuilder.not(`in`)
                }
            }
        }

        fun notInProcurement(procurementId: Int?): Specification<Goods> {
            return Specification<Goods> { root, query, criteriaBuilder ->
                if (procurementId == null) {
                    null
                } else {
                    val subquery = query.subquery(Int::class.java)
                    val procurementRoot = subquery.from(Procurement::class.java)
                    val procurementItemJoin =
                        procurementRoot.joinOnce<Procurement, ProcurementItem>(
                            "items"
                        )
                    subquery.select(
                        procurementItemJoin.joinOnce<ProcurementItem, Goods>(
                            "goods"
                        ).get("id")
                    ).where(
                        criteriaBuilder.equal(
                            procurementRoot.get<Int>("id"),
                            procurementId,
                        ),
                    )
                    val `in` = criteriaBuilder.`in`<Int>(root.get("id"))
                    `in`.value(subquery)
                    criteriaBuilder.not(`in`)
                }
            }
        }

        fun nameContains(name: String?): Specification<Goods> {
            return nameContains(name) { root -> root }
        }

        fun nameOrSubGoodsNameContains(name: String?): Specification<Goods> {
            return nameOrSubGoodsNameContains(name) { root -> root }
        }

        fun nameOrBarcodeWithSubGoodsContains(nameAndBarcode: String?): Specification<Goods> {
            return nameOrBarcodeWithSubGoodsContains(nameAndBarcode) { root -> root }
        }

        fun <E : Any, T : Any> nameOrBarcodeWithSubGoodsContains(
            nameOrBarcode: String?,
            pathBuilder: FromBuilder<E, T, Goods>
        ): Specification<E> {
            if (nameOrBarcode.isNullOrBlank()) return SpecificationUtils.empty()
            return Specification<E> { root, query, criteriaBuilder ->
                val goodsPath = pathBuilder(root)
                val subquery = query.subquery(Int::class.java)
                val subqueryRoot = subquery.from(SubGoods::class.java)
                subquery.where(
                    criteriaBuilder.equal(
                        subqueryRoot.joinOnce<SubGoods, Goods>("goods").get<Int>("id"),
                        goodsPath.get<Int>("id")
                    ),
                    criteriaBuilder.or(
                        criteriaBuilder.like(subqueryRoot.get<String>("name"), "%$nameOrBarcode%"),
                        criteriaBuilder.like(subqueryRoot.get<String>("number"), "%$nameOrBarcode%"),
                    )
                )

                criteriaBuilder.or(
                    criteriaBuilder.exists(subquery), criteriaBuilder.like(
                        root.get("name"),
                        "%$nameOrBarcode%"
                    ),
                    criteriaBuilder.like(root.get("barcode"), "%$nameOrBarcode%")
                )
            }
        }

        fun <E : Any, T : Any> nameOrSubGoodsNameContains(
            name: String?,
            pathBuilder: FromBuilder<E, T, Goods>
        ): Specification<E> {
            if (name == null) return SpecificationUtils.empty()
            return Specification<E> { root, query, criteriaBuilder ->
                val goodsPath = pathBuilder(root)
                val subquery = query.subquery(Int::class.java)
                val subqueryRoot = subquery.from(SubGoods::class.java)
                subquery.where(
                    criteriaBuilder.equal(
                        subqueryRoot.joinOnce<SubGoods, Goods>("goods").get<Int>("id"),
                        goodsPath.get<Int>("id")
                    ), criteriaBuilder.like(subqueryRoot.get<String>("name"), "%$name%")
                )
                val subquery2 = query.subquery(Int::class.java)
                val subqueryRoot2 = subquery.from(SubGoods::class.java)
                criteriaBuilder.equal(
                    subqueryRoot.joinOnce<SubGoods, Goods>("goods").get<Int>("id"),
                    goodsPath.get<Int>("id")
                )

                criteriaBuilder.exists(subquery)
            }.or(nameContains(name, pathBuilder))
        }

        fun <E : Any, T : Any> nameContains(name: String?, pathBuilder: FromBuilder<E, T, Goods>): Specification<E> {
            return SpecificationUtils.contains({ root -> pathBuilder(root).get("name") }, name)
        }

        fun barcodeContains(barcode: String?): Specification<Goods> {
            return barcodeContains(barcode) { root -> root }
        }

        fun barcodeOrSubGoodsNumberContains(barcode: String?): Specification<Goods> {
            return barcodeOrSubGoodsNumberContains(barcode) { root -> root }
        }

        fun disable(disable: Boolean?): Specification<Goods> {
            return if (disable == null) SpecificationUtils.empty() else if (disable) isDisable() else isEnable()
        }

        fun isEnable(): Specification<Goods> {
            return Specification<Goods> { root, query, criteriaBuilder ->
                val goodsBrandJoin = SpecificationUtils.join<Goods, GoodsBrand>(root, "brand", JoinType.LEFT)
                val factoryJoin = SpecificationUtils.join<GoodsBrand, Factory>(goodsBrandJoin, "factory",JoinType.LEFT)
                criteriaBuilder.and(
                    criteriaBuilder.isFalse(root.get("disable")),
                    criteriaBuilder.or(
                        criteriaBuilder.isNull(goodsBrandJoin),
                        criteriaBuilder.isFalse(goodsBrandJoin.get("disable"))
                    ),
                    criteriaBuilder.or(
                        criteriaBuilder.isNull(factoryJoin),
                        criteriaBuilder.isFalse(
                            factoryJoin.get("disable")
                        ),
                    )
                )
            }
        }

        fun isDisable(): Specification<Goods> {
            return Specification<Goods> { root, query, criteriaBuilder ->
                val goodsBrandJoin = SpecificationUtils.join<Goods, GoodsBrand>(root, "brand")
                criteriaBuilder.or(
                    criteriaBuilder.isTrue(root.get("disable")),
                    criteriaBuilder.isTrue(goodsBrandJoin.get("disable")),
                    criteriaBuilder.isTrue(
                        SpecificationUtils.join<GoodsBrand, Factory>(goodsBrandJoin, "factory").get("disable")
                    )
                )
            }
        }

        fun <E : Any, T : Any> barcodeOrSubGoodsNumberContains(
            barcode: String?,
            pathBuilder: FromBuilder<E, T, Goods>
        ): Specification<E> {
            if (barcode.isNullOrBlank()) return SpecificationUtils.empty()
            return Specification<E> { root, query, criteriaBuilder ->
                val goodsPath = pathBuilder(root)
                val subquery = query.subquery(Int::class.java)
                val subqueryRoot = subquery.from(SubGoods::class.java)
                subquery.where(
                    criteriaBuilder.equal(
                        subqueryRoot.joinOnce<SubGoods, Goods>("goods").get<Int>("id"),
                        goodsPath.get<Int>("id")
                    ), criteriaBuilder.like(subqueryRoot.get<String>("number"), "%$barcode%")
                )
                criteriaBuilder.exists(subquery)
            }.or(barcodeContains(barcode, pathBuilder))
        }


        fun <E : Any> barcodeContains(
            barcode: String?,
            pathBuilder: PathBuilder<E, Goods> = { root -> root as Path<Goods> }
        ): Specification<E> {
            return SpecificationUtils.contains({ root -> pathBuilder(root).get("barcode") }, barcode)
        }

        fun brandIn(brandIds: List<Int>): Specification<Goods> {
            return brandIn(brandIds) { root -> root as From<Goods, Goods> }
        }

        fun <E : Any, F> brandIn(brandIds: List<Int>, pathBuilder: FromBuilder<E, F, Goods>): Specification<E> {
            return SpecificationUtils.`in`({ root ->
                pathBuilder(root).joinOnce<Goods, GoodsBrand>("brand").get("id")
            }, brandIds)
        }

        fun categoryIn(categoryIds: List<Int>): Specification<Goods> {
            return categoryIn(categoryIds) { root -> root }
        }

        fun <E, F> categoryIn(categoryIds: List<Int>, pathBuilder: FromBuilder<E, F, Goods>): Specification<E> {
            return SpecificationUtils.`in`({ root ->
                pathBuilder(root).joinOnce<Goods, GoodsCategory>("category").get("id")
            }, categoryIds)
        }

        fun factoryIn(factoryIds: List<Int>): Specification<Goods> {
            return factoryIn(factoryIds) { root -> root }
        }

        fun <E, F> factoryIn(factoryIds: List<Int>, pathBuilder: FromBuilder<E, F, Goods>): Specification<E> {
            return SpecificationUtils.`in`({ root ->
                pathBuilder(root).joinOnce<Goods, GoodsBrand>("brand").joinOnce<GoodsBrand, Factory>("factory")
                    .get("id")
            }, factoryIds)
        }
    }

}

data class GoodsAndMarketInStore(
    val goods: Goods,
    val amountInMarket: Int?
)

data class GoodsSummaryWithPrice(
    val id: Int,
    val name: String,
    val barcode: String,
    val price: BigDecimal,
    val rememberedPrice: BigDecimal?,
)

data class GoodsSummaryWithRememberedPurchasePrice(
    val goods: Goods,
    val subGoods: SubGoods?,
    val rememberedPurchasePrice: BigDecimal?,
    val rememberedUnitId: Long?,
    val rememberedAmount: Int?,
)

data class GoodsSummaryWithRememberedReturnPrice(
    val goods: Goods,
    val price: BigDecimal?,
)

data class GoodsSummaryWithMarketInStore(
    val id: Int,
    val name: String,
    val barcode: String,
    val amount: Int,
    val amountInMarket: Int?,
    val units: List<QueryGoodsUnitResponse>,
    val subGoodsList: List<SubGoodsResponse>
) {

    companion object {
        fun fromGoodsAndMarketInStore(goodsAndMarketInStore: GoodsAndMarketInStore): GoodsSummaryWithMarketInStore {
            return with(goodsAndMarketInStore.goods) {
                GoodsSummaryWithMarketInStore(
                    id,
                    name,
                    barcode,
                    amount,
                    goodsAndMarketInStore.amountInMarket,
                    units.map {
                        QueryGoodsUnitResponse(it.id!!, it.name, it.basicMultiple)
                    },
                    subGoodsList.map {
                        SubGoodsResponse.fromSubGoods(it)
                    },
                )
            }
        }
    }
}

data class QueryGoodsUnitResponse(
    val id: Long,
    val name: String,
    val basicMultiple: Int
) {
    companion object {
        fun formGoodsUnit(goodsUnit: GoodsUnit): QueryGoodsUnitResponse {
            return QueryGoodsUnitResponse(goodsUnit.id!!, goodsUnit.name, goodsUnit.basicMultiple)
        }
    }
}

fun GoodsUnit.toQueryGoodsUnitResponse(): QueryGoodsUnitResponse {
    return QueryGoodsUnitResponse.formGoodsUnit(this)
}


interface CustomGoodsRepository {
    fun findAllWithRememberedProcurementReturnPrice(supplierId: Int): List<RememberedProcurementReturnPriceWithDto>
}

class CustomGoodsRepositoryImpl : CustomGoodsRepository {
    @PersistenceContext
    lateinit var entityManager: EntityManager
    override fun findAllWithRememberedProcurementReturnPrice(supplierId: Int): List<RememberedProcurementReturnPriceWithDto> {
        val query = entityManager.createNamedQuery("Goods.findAllWithRememberedProcurementReturnPrice")
        query.setParameter(1, supplierId)
        val resultList = query
            .resultList as List<Array<Any>>
        return resultList.map {
            RememberedProcurementReturnPriceWithDto(it[0] as Goods, it[1] as SubGoods?, it[2] as BigDecimal?)
        }
    }

}